﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using NGenORM.Library.Util;
using NGenORM.Library.Enum;

namespace NGenORM.Library.Code
{
	public class DBRead
	{

        public Project Project { get; set; }
        private GenericDAL genericDAL { get; set; }

        public DBRead() {
            CreateGenericDAL();
        }

        private void CreateGenericDAL() { 
            genericDAL = new GenericDAL();
        }

        private string GetCodeObjectDBType(ObjectDBType ObjectDBType) {
            switch (ObjectDBType) {
                case ObjectDBType.Table:
                    return "U";
                case ObjectDBType.Procedure:
                    return "P";
                case ObjectDBType.View:
                    return "V";
                case ObjectDBType.Function:
                    return "FN";
                default:
                    return "";
            } 
        }

        private ObjectDBType GetObjectDBTypeCode(string code)
        {
            switch (code)
            {
                case "U":
                    return ObjectDBType.Table;
                    
                case "P":
                    return ObjectDBType.Procedure;
                case "V":
                    return ObjectDBType.View;
                case "FN":
                    return ObjectDBType.Function;
                default:
                    return ObjectDBType.Table;
            }
        }

        /// <summary>
        /// Select One Object By Name
        /// </summary>
        /// <returns>Return Obj</returns>
        /// <remarks></remarks>
        public Obj ListObjectByName(string name) {

            switch (Project.DBServerType)
            {
                case DBServerType.MySQL:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME, SYSOBJECTS.XTYPE
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.NAME = @TABLE_NAME
                        ORDER BY SYSOBJECTS.NAME";
                    genericDAL.AddParameter("@TABLE_NAME", name, SqlDbType.VarChar);
                    break;
                case DBServerType.SQLServer:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME, SYSOBJECTS.XTYPE
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.NAME = @TABLE_NAME
                        ORDER BY SYSOBJECTS.NAME";
                    genericDAL.AddParameter("@TABLE_NAME", name, SqlDbType.VarChar);
                    break;
            }

            genericDAL.objConnectionFactory = Project.ConnectionFactory;
            SqlDataReader dr = genericDAL.ExecuteReader();

            Obj ObjAux = null;

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ObjAux.ObjectName = dr["TABLENAME"].ToString();
                    ObjAux.Project = Project;
                    ObjAux.ObjectDBType = GetObjectDBTypeCode(dr["XTYPE"].ToString());
                }
            }

            return ObjAux;

        }

        public List<Obj> ListAllObjects()
        {

            switch (Project.DBServerType)
            {
                case DBServerType.MySQL:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME, SYSOBJECTS.XTYPE
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE IN ('U', 'P', 'V', 'FN')
                        ORDER BY SYSOBJECTS.NAME";
                    
                    break;
                case DBServerType.SQLServer:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME, SYSOBJECTS.XTYPE
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE IN ('U', 'P', 'V', 'FN')
                        ORDER BY SYSOBJECTS.NAME";
                    break;
            }

            genericDAL.objConnectionFactory = Project.ConnectionFactory;
            SqlDataReader dr = genericDAL.ExecuteReader();

            Obj ObjAux = null;
            List<Obj> Objects = new List<Obj>();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ObjAux = new Obj();
                    ObjAux.ObjectName = dr["TABLENAME"].ToString();
                    ObjAux.Project = Project;
                    ObjAux.ObjectDBType = GetObjectDBTypeCode(dr["XTYPE"].ToString());
                    Objects.Add(ObjAux);
                }
            }

            return Objects;

        }

        public List<Obj> ListAllObjectsByObjectDBType(ObjectDBType ObjectDBType)
        {

            switch (Project.DBServerType) {
                case DBServerType.MySQL:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE = @TYPE 
                        ORDER BY SYSOBJECTS.NAME";
                    genericDAL.AddParameter("@TYPE", GetCodeObjectDBType(ObjectDBType), SqlDbType.VarChar);
                    break;
                case DBServerType.SQLServer:
                    genericDAL.StringSQL = @"SELECT DISTINCT SYSOBJECTS.NAME AS TABLENAME
                        FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME 
                        WHERE SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE = @TYPE 
                        ORDER BY SYSOBJECTS.NAME";
                    genericDAL.AddParameter("@TYPE", GetCodeObjectDBType(ObjectDBType), SqlDbType.VarChar);
                    break;
            }

            SqlDataReader dr = genericDAL.ExecuteReader();

            List<Obj> Objects = new List<Obj>();
            Obj ObjAux = null;

            if (dr.HasRows) {
                while (dr.Read()) {
                    ObjAux = new Obj();
                    ObjAux.ObjectName = dr["TABLENAME"].ToString();
                    ObjAux.Project = Project;
                    ObjAux.ObjectDBType = ObjectDBType;
                    Objects.Add(ObjAux);
                }
            }

            return Objects;

        }

        /// <summary>
        /// Select One Table With Features
        /// </summary>
        /// <returns>Return SqlDataReader</returns>
        /// <remarks></remarks>
        public List<Column> ListAllColumn(Obj obj)
        {

            switch (obj.Project.DBServerType) {
                case DBServerType.MySQL: //MySql > 5.1
                    genericDAL.StringSQL = @"SELECT SYSOBJECTS.NAME AS TABLENAME, SYSCOLUMNS.NAME AS COLNAME, SYSTYPES.XTYPE, CASE SYSOBJECTS.XTYPE WHEN 'U' THEN SYSCOLUMNS.ISNULLABLE ELSE 0 END ISNULLABLE, CASE WHEN TB_PK.TABLE_NAME IS NULL THEN 0 ELSE 1 END AS ISPRIMARYKEY, SYSOBJECTS.XTYPE" + 
                                    " FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME " +
                                    " WHERE SYSOBJECTS.NAME = @TABLE_NAME AND SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE = @TYPE " +
                                    " ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLORDER ";
                    genericDAL.AddParameter("@TABLE_NAME", obj.ObjectName.ToString(), SqlDbType.VarChar);
                    genericDAL.AddParameter("@TYPE", obj.ObjectDBType, SqlDbType.VarChar);
                    break;
                case DBServerType.SQLServer: //Sql Server 2000/2005/2008
                    genericDAL.StringSQL = @"SELECT SYSOBJECTS.NAME AS TABLENAME, SYSCOLUMNS.NAME AS COLNAME, SYSTYPES.XTYPE, CASE SYSOBJECTS.XTYPE WHEN 'U' THEN SYSCOLUMNS.ISNULLABLE ELSE 0 END ISNULLABLE, CASE WHEN TB_PK.TABLE_NAME IS NULL THEN 0 ELSE 1 END AS ISPRIMARYKEY, SYSOBJECTS.XTYPE" + 
                                    " FROM SYSCOLUMNS WITH (NOLOCK) INNER JOIN SYSOBJECTS WITH (NOLOCK) ON SYSCOLUMNS.ID = SYSOBJECTS.ID INNER JOIN SYSTYPES WITH (NOLOCK) ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE LEFT OUTER JOIN (SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WITH (NOLOCK) INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU WITH (NOLOCK) ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA AND KCU.TABLE_NAME = TC.TABLE_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY') TB_PK ON TB_PK.TABLE_NAME = SYSOBJECTS.NAME AND TB_PK.COLUMN_NAME = SYSCOLUMNS.NAME " +
                                    " WHERE SYSOBJECTS.NAME = @TABLE_NAME AND SYSTYPES.STATUS <> 1 AND SYSOBJECTS.XTYPE = @TYPE " +
                                    " ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLORDER ";
                    genericDAL.AddParameter("@TABLE_NAME", obj.ObjectName.ToString(), SqlDbType.VarChar);
                    genericDAL.AddParameter("@TYPE", obj.ObjectDBType, SqlDbType.VarChar);
                    break;
            }

            SqlDataReader dr = genericDAL.ExecuteReader();

            List<Column> Columns = new List<Column>();
            Column columnAux = null;

            if (dr.HasRows) {
                while (dr.Read()) {
                    columnAux.ColumnName = dr["COLNAME"].ToString();
                    columnAux.Nullable = (bool)dr["ISNULLABLE"];
                    columnAux.PrimaryKey = (bool)dr["ISPRIMARYKEY"];
                    columnAux.SqlDBType = (int)dr["XTYPE"];
                    Columns.Add(columnAux);
                }
            }

            dr.Close();
            return Columns;

        }

		///''' <summary>
		///''' Select All Databases on DBServer
		///''' </summary>
		///''' <returns>Return SqlDataReader</returns>
		///''' <remarks></remarks>
		public List<string> ListAllDatabase() {

			List<string> list = new List<string>();

			switch (Project.DBServerType)
			{
				case DBServerType.MySQL:
					genericDAL.StringSQL = @"SELECT UPPER(name) AS Name
										FROM [master].dbo.sysdatabases 
										WHERE 1=1
										ORDER BY name";
					break;

				case DBServerType.SQLServer:
					genericDAL.StringSQL = @"SELECT UPPER(name) AS Name 
                                         FROM [master].dbo.sysdatabases 
                                         WHERE 1=1 
                                         ORDER BY name";
					break;

			}

            genericDAL.objConnectionFactory = Project.ConnectionFactory;
			SqlDataReader dr = genericDAL.ExecuteReader();

			if (dr.HasRows) {
				while (dr.Read()) {
					list.Add(dr["Name"].ToString());                
				}
			}

			dr.Close();

			return list;
		
		}

	}
}
